package com.tool.cn.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * Excel工具 类
 * 
 * @author wangzh 2016年9月18日 上午9:12:59
 */
@SuppressWarnings("all")
public final class ExcelUtil {

	private static final Log log = LogFactory.getLog(ExcelUtil.class);

	/**
	 * 创建xls文件 2003前版本
	 * @param name
	 */
	public static void createExcel(String name) {
		try {
			FileOutputStream output = new FileOutputStream(name);
			HSSFWorkbook wkb = new HSSFWorkbook();
			HSSFSheet sheet = wkb.createSheet("节点列表");

			//设置列宽
			sheet.setColumnWidth(0, 20 * 256);
			sheet.setColumnWidth(1, 20 * 256);
			sheet.setColumnWidth(2, 20 * 256);
			sheet.setColumnWidth(3, 20 * 256);
			sheet.setColumnWidth(4, 30 * 256);
			
			//设置样式
			HSSFCellStyle style = wkb.createCellStyle();
			style.setAlignment(HorizontalAlignment.CENTER);  //水平居中
			style.setVerticalAlignment(VerticalAlignment.CENTER);  //垂直居中
			
		    style.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置填充模式，模式为全部前景色
			style.setFillForegroundColor(HSSFColorPredefined.YELLOW.getColor().getIndex());  // 设置前景色为黄色
			
			//设置边框样式
			style.setBorderBottom(BorderStyle.THIN);
			style.setBorderRight(BorderStyle.THIN);
			style.setBorderTop(BorderStyle.THIN);
			style.setBorderLeft(BorderStyle.THIN);
//			style.setLeftBorderColor();
			
			//创建行
			HSSFRow row2 = sheet.createRow(0);
			
			//创建单元格
			HSSFCell cell = row2.createCell(0);
			cell.setCellValue("ip");
			cell.setCellStyle(style);
			
			cell = row2.createCell(1);
			cell.setCellValue("端口");
			cell.setCellStyle(style);
			
			cell = row2.createCell(2);
			cell.setCellValue("用户名");
			cell.setCellStyle(style);
			
			cell = row2.createCell(3);
			cell.setCellValue("密码");
			cell.setCellStyle(style);
			
			cell = row2.createCell(4);
			cell.setCellValue("说明");
			cell.setCellStyle(style);
			
			wkb.write(output);
			output.flush();
			output.close();
			wkb.close();
		} catch (FileNotFoundException e) {
			log.error("未发现文件" + e);
		} catch (IOException e) {
			log.error("创建excel读取异常" + e);
		}
	}

	/**
	 * 创建xlsx文件 2007后版本
	 * @param name
	 */
	public static void createExcelXlsx(String name) {
		try {
			FileOutputStream output = new FileOutputStream(name);
			XSSFWorkbook workbook = new XSSFWorkbook();
			XSSFSheet  sheet = workbook.createSheet("节点列表");

			//设置列宽
			sheet.setColumnWidth(0, 20 * 256);
			sheet.setColumnWidth(1, 20 * 256);
			sheet.setColumnWidth(2, 20 * 256);
			sheet.setColumnWidth(3, 20 * 256);
			sheet.setColumnWidth(4, 30 * 256);
			
			//设置样式
			XSSFCellStyle style = workbook.createCellStyle();
			style.setAlignment(HorizontalAlignment.CENTER);  //水平居中
			style.setVerticalAlignment(VerticalAlignment.CENTER);  //垂直居中

			style.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置填充模式，模式为全部前景色
			style.setFillForegroundColor(HSSFColorPredefined.YELLOW.getColor().getIndex());  // 设置前景色为黄色

			//设置边框样式
			style.setBorderBottom(BorderStyle.THIN);
			style.setBorderRight(BorderStyle.THIN);
			style.setBorderTop(BorderStyle.THIN);
			style.setBorderLeft(BorderStyle.THIN);
//			style.setLeftBorderColor();
			
			//创建行
			XSSFRow  row2 = sheet.createRow(0);
			
			//创建单元格
			XSSFCell  cell = row2.createCell(0);
			cell.setCellValue("ip");
			cell.setCellStyle(style);
			
			cell = row2.createCell(1);
			cell.setCellValue("端口");
			cell.setCellStyle(style);
			
			cell = row2.createCell(2);
			cell.setCellValue("用户名");
			cell.setCellStyle(style);
			
			cell = row2.createCell(3);
			cell.setCellValue("密码");
			cell.setCellStyle(style);
			
			cell = row2.createCell(4);
			cell.setCellValue("说明");
			cell.setCellStyle(style);
			
			workbook.write(output);
			output.flush();
			output.close();
			workbook.close();
		} catch (FileNotFoundException e) {
			log.error("未发现文件" + e);
		} catch (IOException e) {
			log.error("创建excel读取异常" + e);
		}
	}
	
	@SuppressWarnings("resource")
	public static List<String> readExcelSecond(String xlsPath) {
		try {
			List<String> temp = new ArrayList<>();

			FileInputStream fileIn = new FileInputStream(xlsPath);
			Workbook wb = null;
			if (checkVersion(xlsPath))
				wb = new XSSFWorkbook(fileIn);  //xlsx
			else {
				wb = new HSSFWorkbook(fileIn);  //xls
			}

			Sheet sht0 = wb.getSheetAt(0);

			for (Row r : sht0) {
				if (r.getRowNum() >= 1) {
					
					//ip
					if ((r.getCell(0) != null) && (r.getCell(0).toString() != "")) {
						System.out.println(r.getCell(0).getStringCellValue().trim());
						temp.add(r.getCell(0).getStringCellValue().trim());
					}
					
					//端口
					if ((r.getCell(1) != null) && (r.getCell(1).toString() != "")) {
						System.out.println((int) r.getCell(1).getNumericCellValue());
					}
					
				}
			}
			fileIn.close();
			wb.close();
			
			return temp;
		} catch (Exception e) {
			log.error(e.toString(), e);
		}
		return null;
	}
	
	 /**
     * 是否为2007版本
     * @param url
     * @return
     */
    public static boolean checkVersion(String url){
        return url.matches("^.+\\.(?i)(xlsx)$");  
    }
	
	/**
	 * 读取excel
	 * @param sheet 表单好从0开始
	 * @param column 列
	 */
	public static List<String> readExcel(String xlsPath,int sheet,int column) {
		List<String > all=new ArrayList<>();
		
		try {
			FileInputStream fileIn = new FileInputStream(xlsPath);
			Workbook wb=null;
			 if(checkVersion(xlsPath)){
                wb=new XSSFWorkbook(fileIn);
            }else{
                wb=new HSSFWorkbook(fileIn);
            }
			
			Sheet sht0 = wb.getSheetAt(sheet); // 获取表单 从0开始
			StringBuffer buf=new StringBuffer();
			String context;
			
			for (Row r : sht0) {
				System.out.println(r.getRowNum());
				if (r.getRowNum() < 1) {
					continue;
				}
				
				if(r.getCell(column)!=null) {
//					context=r.getCell(column).getStringCellValue();  //指定类型获取，类错误时，报错
					context=r.getCell(column).toString();  //统一字符串获取
					all.add(context);
				}
				
			}
			fileIn.close();
//			return buf.toString();
		} catch (FileNotFoundException e) {
			log.error("文件不存在或文件不可操作" + e,e);
		} catch (IOException e) {
			log.error("读写IO流异常" + e,e);
		} catch (Exception e) {
			log.error(e);
		}
		return all;
	}
}
